﻿
using OA.Core;
using OA.Model;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using OA.Core.Helper;
using OA.Model.MyAttribute;

namespace OA.BLL
{
    public class ExcelColumnInfo
    {
        /// <summary>
        /// 字段表头名称
        /// </summary>
        public string Title { get; set; }
        /// <summary>
        /// 属性 对象的属性名称 如果title为 Excel_Head 表示头部  Excel_Foot 表示底部
        /// </summary>
        public string Property { get; set; }

        /// <summary>
        /// 单元格宽度
        /// </summary>
        public int Width { get; set; } = 12 * 256;

        /// <summary>
        /// 字体大小
        /// </summary>
        public short ItemFontHeightInPoints { get; set; } = 12;

        /// <summary>
        /// 字体名称
        /// </summary>
        public string ItemFontName { get; set; } = "微软雅黑";

        public short ItemBoldweight { get; set; } = 150;

        /// <summary>
        ///None = -1,,Top = 0, Center = 1, Bottom = 2,Justify = 3,Distributed = 4
        /// </summary>
        public int ItemVerticalAlignment { get; set; } = 1;

        /// <summary>
        ///General = 0, Left = 1,Center = 2, Right = 3,Fill = 4,Justify = 5,CenterSelection = 6,Distributed = 7
        /// </summary>
        public int ItemHorizontalAlignment { get; set; } = 1;

        public bool ItemWrapText { get; set; } = true;


        /// <summary>
        /// 字体大小
        /// </summary>
        public short HeadFontHeightInPoints { get; set; } = 14;

        /// <summary>
        /// 字体名称
        /// </summary>
        public string HeadFontName { get; set; } = "微软雅黑";

        public short HeadBoldweight { get; set; } = 200;

        /// <summary>
        ///None = -1,,Top = 0, Center = 1, Bottom = 2,Justify = 3,Distributed = 4
        /// </summary>
        public int HeadVerticalAlignment { get; set; } = 1;

        /// <summary>
        ///General = 0, Left = 1,Center = 2, Right = 3,Fill = 4,Justify = 5,CenterSelection = 6,Distributed = 7
        /// </summary>
        public int HeadHorizontalAlignment { get; set; } = 1;

        public bool HeadWrapText { get; set; } = true;

    }
    public class ExcelHelper
    {
        /// <summary>
        /// 导出Excel
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="title">Excel 标题</param>
        /// <param name="list">导出数据列表</param>
        /// <param name="listExcelColumnInfo">导出字段对象</param>
        /// <param name="tfootContent">底部内容</param>
        /// <returns></returns>
        public static MemoryStream Export<T>(List<T> list,List<ExcelColumnInfo> listExcelColumnInfo)
        {
            List<ExcelColumnInfo> listExcelColumnInfo2 = listExcelColumnInfo.Where(m => m.Property.ToLower() != "excel_foot" && m.Property.ToLower() != "excel_head").ToList();
            string title = "sheet1";
            var titleInfo = listExcelColumnInfo.Where(m => m.Property.ToLower() == "excel_head").FirstOrDefault();
            if (titleInfo != null)
            {
                title = titleInfo.Title;
            }
            string footContent = string.Empty;
            var footInfo = listExcelColumnInfo.Where(m => m.Property.ToLower() == "excel_foot").FirstOrDefault();
            if (footInfo != null)
            {
                footContent = footInfo.Title;
            }
            HSSFWorkbook book = new HSSFWorkbook();
            ISheet sheet = book.CreateSheet(title);
                       

            ///保存样式
            Dictionary<string, ICellStyle> dicHead = new Dictionary<string, ICellStyle>();
            Dictionary<string, ICellStyle> dicItem = new Dictionary<string, ICellStyle>();
            
            foreach (ExcelColumnInfo info in listExcelColumnInfo)
            {
                string property = info.Property.ToLower();
                if (dicHead.ContainsKey(property)) continue;
                IFont titleFontTitle = book.CreateFont();
                titleFontTitle.FontHeightInPoints = info.HeadFontHeightInPoints;
                titleFontTitle.FontName = info.HeadFontName;
                titleFontTitle.Boldweight = info.HeadBoldweight;
                ICellStyle titleCellStyle = book.CreateCellStyle();
                titleCellStyle.VerticalAlignment = (VerticalAlignment)info.HeadVerticalAlignment;
                titleCellStyle.Alignment = (HorizontalAlignment)info.HeadHorizontalAlignment;
                titleCellStyle.SetFont(titleFontTitle);
                titleCellStyle.WrapText = info.HeadWrapText;


                IFont itemFontTitle = book.CreateFont();
                itemFontTitle.FontHeightInPoints = info.ItemFontHeightInPoints;
                itemFontTitle.FontName = info.ItemFontName;
                itemFontTitle.Boldweight = info.ItemBoldweight;
                ICellStyle itemCellStyle = book.CreateCellStyle();
                itemCellStyle.VerticalAlignment = (VerticalAlignment)info.ItemVerticalAlignment;
                itemCellStyle.Alignment = (HorizontalAlignment)info.ItemHorizontalAlignment;
                itemCellStyle.SetFont(itemFontTitle);
                itemCellStyle.WrapText = info.ItemWrapText;
                dicHead.Add(property, titleCellStyle);
                dicItem.Add(property, itemCellStyle);
            }

            ///初始化列宽度
            for (int i = 0; i < listExcelColumnInfo2.Count; i++)
            {
                sheet.SetColumnWidth(i, listExcelColumnInfo2[i].Width);
            }

            int rowIndex = 0;
            //打印标题
            if (dicHead.ContainsKey("excel_head"))
            {
                IRow rowTitle = sheet.CreateRow(0);
                ICell cellTitle = rowTitle.CreateCell(0);
                cellTitle.CellStyle = dicHead["excel_head"];
                cellTitle.SetCellValue(title);
                sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, listExcelColumnInfo2.Count - 1));
                rowIndex++;
            }

            

            ///打印表头
            IRow itemRow = sheet.CreateRow(rowIndex);
            for (int i = 0; i < listExcelColumnInfo2.Count; i++)
            {
                string itemProperty = listExcelColumnInfo2[i].Property.ToLower();
                ICell item = itemRow.CreateCell(i);
                item.CellStyle = dicHead[listExcelColumnInfo2[i].Property.ToLower()];
                item.SetCellValue(listExcelColumnInfo2[i].Title);
            }
            rowIndex++;

            //打印详情
            foreach (T t in list)
            {
                IRow item = sheet.CreateRow(rowIndex);
                var Properties = t.GetType().GetProperties();
                for (int i = 0; i < listExcelColumnInfo2.Count; i++)
                {
                    string itemProperty = listExcelColumnInfo2[i].Property.ToLower();
                    var property = Properties.Where(m => m.Name.ToLower() == itemProperty).FirstOrDefault();
                    if (property != null)
                    {
                        ICell itemCell = item.CreateCell(i);
                        itemCell.CellStyle =dicItem[listExcelColumnInfo2[i].Property.ToLower()];
                        itemCell.SetCellValue(property.GetValue(t).ToString());
                    }
                    else
                    {
                        ICell itemCell = item.CreateCell(i);
                        itemCell.SetCellValue("");
                    }
                }
                rowIndex++;
            }

            
            if (footInfo != null)
            {                
                IRow rowFoot = sheet.CreateRow(rowIndex);
                ICell cellFoot = rowFoot.CreateCell(0);
                if (dicHead.ContainsKey("excel_foot"))
                {
                    cellFoot.CellStyle = dicHead["excel_foot"];
                }
                cellFoot.SetCellValue(footContent);
                sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 0, listExcelColumnInfo2.Count - 1));
            }

            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            book.Write(ms);
            ms.Seek(0, SeekOrigin.Begin);
            return ms;
            //return new FileStreamResult(ms, "application/vnd.ms-excel");
        }



        public static MemoryStream ExportCustomForm(string title, CustomForm infoCustomForm, List<Voucher> list, List<string> field)
        {
            
            HSSFWorkbook book = new HSSFWorkbook();
            
            ISheet sheet = book.CreateSheet(title);

             

            List<ExtendField> listField = new List<ExtendField>();
            if (field.Contains("ID")) {listField.Add(new ExtendField() { Name="ID",ParentName="ID",Attribute="ID"});}
            if (field.Contains("No")) { listField.Add(new ExtendField() { Name = "编号", ParentName = "编号", Attribute = "No" }); }
            if (field.Contains("Title")) { listField.Add(new ExtendField() { Name = "标题", ParentName = "标题", Attribute = "Title" }); }
            if (field.Contains("ApplicationDate")) { listField.Add(new ExtendField() { Name = "发起时间", ParentName = "发起时间", Attribute = "ApplicationDate" }); }
            if (field.Contains("FinishTime")) { listField.Add(new ExtendField() { Name = "完成时间", ParentName = "完成时间", Attribute = "FinishTime" }); }
            if (field.Contains("SponsorName")) { listField.Add(new ExtendField() { Name = "发起人", ParentName = "发起人", Attribute = "SponsorName" }); }
            if (field.Contains("PositionID")) { listField.Add(new ExtendField() { Name = "岗位ID", ParentName = "岗位ID", Attribute = "PositionID" }); }
            if (field.Contains("DepartmentID")) { listField.Add(new ExtendField() { Name = "部门ID", ParentName = "部门ID", Attribute = "DepartmentID" }); }

            CustomFormExtendInfo extendInfo = null;
            if (!string.IsNullOrWhiteSpace(infoCustomForm.ExtendField))
            {
                extendInfo = JsonHelper.DeserializeObj<CustomFormExtendInfo>(infoCustomForm.ExtendField);
            }

            if (extendInfo == null)
            {
                extendInfo = new CustomFormExtendInfo();
            }


            foreach (ExtendField item in extendInfo.ListExtendField.OrderBy(m=>m.Sort).ToList())
            {
                item.ParentName = item.Name;
                listField.Add(item);
            }
            foreach (ExtendField item in extendInfo.TableFields.OrderBy(m => m.Sort).ToList())
            {
                listField.Add(item);
            }

            if (field.Contains("Description")) { listField.Add(new ExtendField() { Name = "描述", ParentName = "描述", Attribute = "Description" }); }

            listField = listField.Where(m => field.Contains(m.Attribute)).ToList();


            IFont titleFont = book.CreateFont();
            titleFont.FontHeightInPoints = 24;
            titleFont.FontName = "微软雅黑";
            titleFont.Boldweight = 100;
            ICellStyle titleCellStyle = book.CreateCellStyle();
            titleCellStyle.VerticalAlignment = VerticalAlignment.Center;
            titleCellStyle.Alignment = HorizontalAlignment.Center;
            titleCellStyle.SetFont(titleFont);
            titleCellStyle.WrapText = true;


            int rowIndex = 0;
            IRow rowTitle = sheet.CreateRow(0);
            ICell cellTitle = rowTitle.CreateCell(0);
            cellTitle.CellStyle = titleCellStyle;
            cellTitle.SetCellValue(title);
            sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, listField.Count - 1));
            rowIndex++;

            IFont smallHeaderFont = book.CreateFont();
            smallHeaderFont.FontHeightInPoints = 12;
            smallHeaderFont.FontName = "微软雅黑";
            smallHeaderFont.Boldweight = 20;
            ICellStyle smallHeaderCellStyle = book.CreateCellStyle();
            smallHeaderCellStyle.VerticalAlignment = VerticalAlignment.Center;
            smallHeaderCellStyle.Alignment = HorizontalAlignment.Center;
            smallHeaderCellStyle.SetFont(smallHeaderFont);
            smallHeaderCellStyle.WrapText = true;


            IFont itemFont = book.CreateFont();
            itemFont.FontHeightInPoints = 9;
            itemFont.FontName = "微软雅黑";
            itemFont.Boldweight = 40;
            ICellStyle itemCellStyle = book.CreateCellStyle();
            itemCellStyle.VerticalAlignment = VerticalAlignment.Center;
            itemCellStyle.Alignment = HorizontalAlignment.Center;
            itemCellStyle.SetFont(itemFont);
            itemCellStyle.WrapText = true;

            int colspan = listField.Count;
            var parents = listField.Where(m => !string.IsNullOrWhiteSpace(m.ParentName)).ToList();
            var myGroups = parents.GroupBy(m => m.ParentName).ToList();
            if(myGroups.Count< listField.Count)
            {
                IFont bigHeaderFont = book.CreateFont();
                bigHeaderFont.FontHeightInPoints = 14;
                bigHeaderFont.FontName = "微软雅黑";
                bigHeaderFont.Boldweight = 20;
                ICellStyle bigHeaderCellStyle = book.CreateCellStyle();
                bigHeaderCellStyle.VerticalAlignment = VerticalAlignment.Center;
                bigHeaderCellStyle.Alignment = HorizontalAlignment.Center;
                bigHeaderCellStyle.SetFont(bigHeaderFont);
                bigHeaderCellStyle.WrapText = true;

                IRow headRow_Big = sheet.CreateRow(rowIndex);
                int headIndex = 0;
                foreach (var group in myGroups)
                {
                    int children = group.Count();
                    string headName = group.Key;
                    sheet.SetColumnWidth(headIndex, headName.Length * 3 * 256);
                    if (headName == "No")
                    {
                        headName = "编号";
                        sheet.SetColumnWidth(headIndex, 5 * 3 * 256);
                    }
                    else if (headName == "Title")
                    {
                        headName = "标题";
                        sheet.SetColumnWidth(headIndex, 10 * 3 * 256);
                    }
                    else if (headName == "ApplicationDate")
                    {
                        headName = "发起时间";
                    }
                    else if (headName == "FinishTime")
                    {
                        headName = "审批、处理完成时间";
                    }
                    else if (headName == "SponsorName")
                    {
                        headName = "发起人";
                    }
                    else if (headName == "Description")
                    {
                        headName = "描述";
                    }
                    if (children == 1)//单元格占2行
                    {
                        ICell tempCell = headRow_Big.CreateCell(headIndex);
                        tempCell.CellStyle = smallHeaderCellStyle;//................................
                        tempCell.SetCellValue(headName);                        
                        sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex+1, headIndex, headIndex));
                        headIndex++;
                    }
                    else
                    {
                        int lastIndex = headIndex + group.Count() - 1;
                        ICell tempCell = headRow_Big.CreateCell(headIndex);
                        tempCell.CellStyle = bigHeaderCellStyle;
                        tempCell.SetCellValue(headName);
                        sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, headIndex, lastIndex));
                        headIndex = headIndex + group.Count();
                    }
                }
                rowIndex++;
            }

           

            int index = 0;
            IRow headRow = sheet.CreateRow(rowIndex);
            foreach (var item in listField)
            {
                index++;
                if (myGroups.Count != listField.Count&& item.Name==item.ParentName)
                {
                    continue;
                }
                ICell tempCell = headRow.CreateCell(index - 1);
                tempCell.CellStyle = smallHeaderCellStyle;
                tempCell.SetCellValue(item.Name);
                if(item.Type== (int)TagEnum.DateTime)
                {
                    sheet.SetColumnWidth(index - 1, 6 * 3 * 256);
                }
                else if (item.Type == (int)TagEnum.Date)
                {
                    sheet.SetColumnWidth(index - 1, 3 * 3 * 256);
                }
                else if (item.Type == (int)TagEnum.Time)
                {
                    sheet.SetColumnWidth(index - 1, 3 * 3 * 256);
                }
                else if (item.Type == (int)TagEnum.Year)
                {
                    sheet.SetColumnWidth(index - 1, 2 * 5 * 256);
                }
                else if(item.Attribute=="No")
                {
                    sheet.SetColumnWidth(index - 1, 5 * 3 * 256);
                }
                else if (item.Attribute == "Title")
                {
                    sheet.SetColumnWidth(index - 1, 10 * 3 * 256);
                }
                else
                {
                    sheet.SetColumnWidth(index - 1, item.Name.Length * 3 * 256);
                }                
            }
            rowIndex++;

            Dictionary<string, string> dicValue = new Dictionary<string, string>();
            foreach (Voucher item in list)
            {
                if (dicValue.ContainsKey("ID")) { dicValue["ID"] = item.ID.ToString(); } else { dicValue.Add("ID", item.ID.ToString()); }
                if (dicValue.ContainsKey("No")) { dicValue["No"] = item.No; } else { dicValue.Add("No", item.No); }
                if (dicValue.ContainsKey("Title")) { dicValue["Title"] = item.Title; } else { dicValue.Add("Title", item.Title); }
                if (dicValue.ContainsKey("ApplicationDate")) { dicValue["ApplicationDate"] = item.ApplicationDate.ToString2(); } else { dicValue.Add("ApplicationDate", item.ApplicationDate.ToString2()); }
                if (dicValue.ContainsKey("PositionID")) { dicValue["PositionID"] = item.PositionID.ToString(); } else { dicValue.Add("PositionID", item.PositionID.ToString()); }
                if (dicValue.ContainsKey("Sponsor")) { dicValue["Sponsor"] = item.Sponsor; } else { dicValue.Add("Sponsor", item.Sponsor); }
                if (dicValue.ContainsKey("DepartmentID")) { dicValue["DepartmentID"] = item.DepartmentID.ToString(); } else { dicValue.Add("DepartmentID", item.DepartmentID.ToString()); }
                if (dicValue.ContainsKey("SponsorName")) { dicValue["SponsorName"] = item.SponsorName; } else { dicValue.Add("SponsorName", item.SponsorName); }
                if (dicValue.ContainsKey("FinishTime")) { dicValue["FinishTime"] = item.FinishTime.ToString2(); } else { dicValue.Add("FinishTime", item.FinishTime.ToString2()); }


                //ExtensionResultInfo infoExtensionResult = new ExtensionResultInfo();
                //if (!string.IsNullOrWhiteSpace(item.Extension))
                //{
                //    infoExtensionResult = JsonHelper.DeserializeObj<ExtensionResultInfo>(item.Extension);
                //}

                //if (infoExtensionResult == null) { infoExtensionResult = new ExtensionResultInfo(); }

                //if(infoExtensionResult.DicExtend != null)
                //{
                //    foreach (var dic in infoExtensionResult.DicExtend)
                //    {
                //        if (dicValue.ContainsKey(dic.Key)) { dicValue[dic.Key] = dic.Value; } else { dicValue.Add(dic.Key, dic.Value); }
                //    }
                //}
                
                //if(infoExtensionResult.TableValues==null)
                //{
                //    infoExtensionResult.TableValues = new List<Dictionary<string, string>>();                     
                //}

            //    foreach (var row in infoExtensionResult.TableValues)
            //    {
            //        IRow itemRow = sheet.CreateRow(rowIndex);
            //        foreach (var dic in row)
            //        {
            //            if (dicValue.ContainsKey(dic.Key)) { dicValue[dic.Key] = dic.Value; } else { dicValue.Add(dic.Key, dic.Value); }
            //        }
            //        int itemIndex = 0;
            //        var keys = listField.Select(m => m.Attribute).ToList();
            //        foreach (string key in keys)
            //        {
            //            string value = string.Empty;
            //            if(dicValue.ContainsKey(key))
            //            {
            //                value = dicValue[key];
            //            }
            //            ICell tempCell = itemRow.CreateCell(itemIndex);
            //            tempCell.CellStyle = itemCellStyle;
            //            tempCell.SetCellValue(value);                        
            //            itemIndex++;
            //        }
            //        rowIndex++;
            //    }
            }

            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            book.Write(ms);
            ms.Seek(0, SeekOrigin.Begin);
            return ms;
             
        }



        public static MemoryStream ExportDiary(string title,  List<Diary> list)
        {

            HSSFWorkbook book = new HSSFWorkbook();

            IFont bigHeaderFont = book.CreateFont();
            bigHeaderFont.FontHeightInPoints = 14;
            bigHeaderFont.FontName = "微软雅黑";
            bigHeaderFont.Boldweight = 20;
            ICellStyle bigHeaderCellStyle = book.CreateCellStyle();
            bigHeaderCellStyle.VerticalAlignment = VerticalAlignment.Center;
            bigHeaderCellStyle.Alignment = HorizontalAlignment.Center;
            bigHeaderCellStyle.SetFont(bigHeaderFont);
            bigHeaderCellStyle.WrapText = true;

            IFont itemFont = book.CreateFont();
            itemFont.FontHeightInPoints = 9;
            itemFont.FontName = "微软雅黑";
            itemFont.Boldweight = 40;
            ICellStyle itemCellStyle = book.CreateCellStyle();
            
            itemCellStyle.VerticalAlignment = VerticalAlignment.Top;
            itemCellStyle.Alignment = HorizontalAlignment.Left;
            itemCellStyle.SetFont(itemFont);
            itemCellStyle.WrapText = true;


            IFont weekFont = book.CreateFont();
            weekFont.FontHeightInPoints = 9;
            weekFont.FontName = "微软雅黑";
            weekFont.Boldweight = 40;
            ICellStyle weekCellStyle = book.CreateCellStyle();
            weekCellStyle.VerticalAlignment = VerticalAlignment.Center;
            weekCellStyle.Alignment = HorizontalAlignment.Center;
            weekCellStyle.SetFont(weekFont);
            weekCellStyle.WrapText = true;

            string[] weeks = { "日","一","二","三","四","五","六"};
            
            
            var groups = list.GroupBy(m => m.EmpName).ToList();           
            foreach (var item in groups)
            {
                int index = 0;
                ISheet sheet = book.CreateSheet(item.Key);
                for (int i = 0; i <= 6; i++)
                {
                    sheet.SetColumnWidth(i, 8 * 3 * 256);
                }
                var monthDiary = item.GroupBy(m => m.DateTime.Substring(0, 7));
                
                foreach (var item2 in monthDiary)
                {
                    DateTime dtNow = DateTime.Now;
                    if (!DateTime.TryParse(item2.Key, out dtNow))
                    {
                        continue;
                    }
                    IRow rowTitle = sheet.CreateRow(index);
                    ICell cellTitle = rowTitle.CreateCell(0);
                    cellTitle.CellStyle = bigHeaderCellStyle;
                    cellTitle.SetCellValue(item2.Key);
                    sheet.AddMergedRegion(new CellRangeAddress(index, index, 0, 6));

                    index = index + 1;

                    IRow weekTitle = sheet.CreateRow(index);
                    for (int i=0;i< weeks.Length;i++)
                    {
                        ICell tempCell = weekTitle.CreateCell(i);
                        tempCell.CellStyle = weekCellStyle;
                        tempCell.SetCellValue(weeks[i]);
                         
                    }
                    index = index + 1;
                    int days = DateTime.DaysInMonth(dtNow.Year, dtNow.Month);
                    IRow itemRow = sheet.CreateRow(index);
                    for (int i=1;i<= days;i++)
                    {
                        DateTime currentDay = new DateTime(dtNow.Year, dtNow.Month, i);
                        string currentDayTitle = currentDay.ToString("yyyy-MM-dd");
                        int weekIndex = (int)currentDay.DayOfWeek;

                        ICell itemCell = itemRow.CreateCell(weekIndex);
                        itemCell.CellStyle = itemCellStyle;
                        StringBuilder sb = new StringBuilder();
                        sb.Append(currentDayTitle);
                        sb.Append("\r\n");
                        Diary myDiary = item2.Where(m => m.DateTime == currentDayTitle).FirstOrDefault();
                        if(myDiary!=null)
                        {
                            sb.Append(myDiary.Details);
                        }
                        itemCell.SetCellValue(sb.ToString());
                        if(weekIndex==6)
                        {
                            index = index + 1;
                            itemRow = sheet.CreateRow(index);
                        }
                    }
                    index = index + 2;
                }                
            }

            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            book.Write(ms);
            ms.Seek(0, SeekOrigin.Begin);
            return ms;

        }
    }
}
